Excel Add-in for Taxprep

1. Access the Taxprep Web site (www.taxprep.com).
2. Click Download and enter your credentials in the "Login" section.
   Once your profile is downloaded, the File Centre page displays.
   
3. In the Taxprep section, click Excel Add-in for Taxprep.
4. Click the setup.exe button and follow the instructions on the screen.

The Taxprep tab now appears on the Microsoft Excel ribbon.

	Indicates the version number of the Taxprep Excel Add-in.
	Allows for e-mailing the Wolters Kluwer Support Centre.
	Allows for displaying the Taxprep online Help Centre in a Web browser.
	Allows for displaying the list of Taxprep versions supported by the Excel Add-in for Taxprep. Note : The Taxprep versions are greyed out when they are not installed on the workstation.
	Allows for displaying examples of Excel spreadsheets created with the Excel Add-in for Taxprep.
	Allows for creating, modifying or deleting connections to a client files.

1. Click the Connection Manager button to open the Connection Manager box.
2. Click the Taxprep Connection button.
3. Select the client file for which you want to create a connection.
4. Enter the name that you want to give to your connection in the Connection Name cell and click Open.
5. Click OK.

1. Click the Connection Manager button to open the Connection Manager box.
2. Select the connection that you want to modify.
3. Click the Edit button.
4. Select the client file that you want to use for the connection and, where applicable, modify the name of the connection in the Connection Name cell.
5. Click Openr.
6. Click OK.

1. Clck the Connection Manager button to open the Connection Manager box.
2. Select the connection that you want to delete.
3. Click the Deletebutton and click OK.

Here are a few examples of available features:

TXP.GETCELL	Allows for importing the content of a Taxprep cell to Excel.
TXP.GETCELLDESCRIPTION	Allows for importing the description of a Taxprep cell to Excel.
TXP.SETCELL	Allows for importing the content of an Excel cell to Taxprep.
TXP.TAXPAYERS	Allows for obtaining the number of taxpayers in the client file.

To access the complete list of functions available in the Excel Add-in for Taxprep, proceed as follows: 

1. In Excel, click the FORMULAS tab.
2. In the toolbar, click Insert Function.
3. In the drop-down list Or select a category, select Taxprep.
   The different functions are now displayed under the area Select Function.
4. Click the function that you want to use to display a short description.
5. Click OK.
   The Function Arguments dialog box displays.
6. In the "Connection" cell, enter the name of the connection that you defined using the Connection Manager.

To copy the ID of a cell from Taxprep, proceed as follows:

1. In Taxprep, access the form in which you want to copy the cell ID.
2. Right-click in the desired cell to display the contextual menu.
3. Click Copy Cell ID.
4. In Excel, right-click to display the contextual menu and click Paste
   or
   Use the Ctrl+V shortcut key.

The add-in provides different Excel functions to interact with your Taxprep files. See this 5 min. video covering these main functions:

• TXP.GetCell("Taxprep File","Taxprep Cell ID")
  For example: TXP.GetCell("C:\Files\JohnSmith.114","T1.Towjac134")
• TXP.GetCellDescription("Taxprep File","Taxprep Cell ID")
• TXP.SetCell("Taxprep File","Taxprep Cell ID",Value)
• TXP.GetCellScenario ("Taxprep File",Scenario#,"Taxprep Cell ID")
• TXP.SetCellScenario ("Taxprep File",Scenario#,"Taxprep Cell ID",Value)

Please note that in a same Excel worksheet, you can refer to multiple Taxprep files at once, eg multiple T1 files, a mix of T1 and T2 files, and so on.

Formulas communicating with Taxprep

= TXP.GETCELL - Gets the value of a Taxprep Cell

= TXP.GETCELLDESCRIPTION - Gets the description of a Taxprep Cell

= TXP.COUNT - Counts the number of copy of a group

= TXP.TAXPAYERS - Gets the number of Taxpayers

= TXP.GETCELLSCENARIO - Gets the value of a Taxprep Cell for a specific scenario

= TXP.GETCELLDESCRIPTIONSCENARIO - Gets the description of a Taxprep Cell

= TXP.SETCELLSCENARIO - Sets the value of a Taxprep Cell for a specific scenario

= TXP.TAXPAYERSSCENARIO - Gets the number of Taxpayers

Formula communicating with CCH ProSystem fx Tax

= PFX.SETCELL - Set the value of a Pfx Cell

Getting data from Spouse and dependents' returns

The formula = TXP.GETCELL allows you to get the value of a cell for the main taxpayer. In Taxprep, the Cell ID for a field (like first name) is the same Cell ID whether that field or cell is located in the main taxpayer's return, the spouse's return or dependents' returns. We need to specifiy something in addition to the Cell ID to tell the Add-in in which return to fetch the value. We do it by specifiying brackets [] in front of the Cell ID containing the index number of the return we want to fetch from. The Main taxpayer's return's index is 1. The spouse's return's index is 2. The first dependent's return's index is 3. And so on.

When there are no spouse in a client file but 2 dependents, the indexes are as follows:

• Index 1 : Main taxpayer
• Index 2 : Dependent 1
• Index 3 : Dependent 2

The formulas to get the first name for these 3 people would be as follows:

=TXP.GETCELL(Connection1, "ID.Towidt9") - Gets the first name of the Main taxpayer

=TXP.GETCELL(Connection1, "[2]ID.Towidt9") - Gets the first name of Dependent 1

=TXP.GETCELL(Connection1, "[3]ID.Towidt9") - Gets the first name of Dependent 2

The formulas would be the same for the following case:

• Index 1 : Main taxpayer
• Index 2 : Spouse
• Index 3 : Dependent 1
• =TXP.GETCELL(Connection1, "ID.Towidt9") - Gets the first name of the Main taxpayer
• =TXP.GETCELL(Connection1, "[2]ID.Towidt9") - Gets the first name of the Spouse
• =TXP.GETCELL(Connection1, "[3]ID.Towidt9") - Gets the first name of Dependent 1

In summary, the index of a client file depends on the returns it contains. This is why we created the formula : =TXP.TAXPAYERS to return the number of Taxpayers present in the client file in order for you to cycle through all the taxpayers within your Excel spreadsheet no matter how many returns it contains.

Formula communicating with CCH ProSystem fx Tax

= PFX.SETCELL - Set the value of a Pfx Cell

= PFX.GETCELL - Get the value of a Pfx Cell

Things to keep in mind

1. Setting the same cell in multiple places is dangerous.
   • There’s no guarantee which setcell will be executed last (and therefore which value will be pushed to Taxprep).
2. Be careful when setting a value you previously got from Taxprep (not to create an infinite loop)
   • Ex: SetCell(A1, GetCell(A1) + 5) will increment A1 by 5 indefinitely.
3. When setting values in linked forms between the Main Taxpayer and the Spouse, always set the values in the Main Taxpayer.
   • Values set in the Spouse will always be overwritten by the existing values in the Main Taxpayer's form.
4. You can’t set a date directly from a date you got from Taxprep. You have to convert it beforehand (to the format YYYY-MM-DD).
   • Not doing so will create an infinite loop because Taxprep won’t accept the date and Excel will try to push it over and over again.
5. Getting a protected cell when it’s value hasn’t been set yet by Taxprep will return N/A.
6. Setting a protected cell will throw a N/A error in Excel.
7. Getting a cell from a repeatable index that doesn’t exist won’t create the missing repeatables.
   • It will throw a N/A error in Excel.
8. Setting a cell in a repeatable index that doesn’t exist will create the missing repeatables.
9. Putting an erroneous cell ID will throw a N/A error in Excel.
10. Using ctrl + alt + F9 will recalculate all cells.
11. All cells are calculated when excel is launched.
12. If you switch to COM mode (using Excel while Taxprep is closed) and reopen Taxprep, all calculations will be done at the moment a cell is triggered.
13. A cell is always triggered when it’s modified and it loses focus.
14. To trigger an unmodified cell manually, click on it, click the formula bar at the top of the spreadsheet and press Enter.
15. It’s good practice to always prepend [1] to the cell ID when referring to the Main Taxpayer.
    • Failing to do so might result in some errors.
    • To refer to the second Taxpayer, prepend [2] (usually the spouse).
16. In Excel, when using an intermediate cell to store the value used in a SetCell, it’s safer to set the intermediate cell’s format to Text (to make sure Excel doesn’t change the formatting).
17. It's safer to use the function SetCellAsDate when setting a date.
    • Set the value's cell format to Date
18. When getting a date from Taxprep, you can set the cell’s format to Date to display the date correctly.
    • When the format is Text, the date will display as a number.
19. If Excel warns you of circular references, failing to fix the issue will prevent recalculations to be automatically done in Taxprep.

Error handling

Possible reasons if Excel shows : 

• N/A
  • Erroneous CellID
    • If pushing in the Main Taxpayer, try putting [1] in front of the cell ID if it’s not already there.
• #REF
  • Trying to write in a protected cell (SetCell).
• #NULL
  • Getting a protected cell with no value (GetCell).
• #NUM
  • The SetCell wasn’t able to set the value in Taxprep to the exact value you specified (make sure the formatting is correct).
    • A popup probably appeared in Taxprep.
• #NAME
  • Make sure the addin is initialised
• #VALUE
  • The addin has trouble communicating with the Taxprep file
    • Make sure you're using an existing connection.
• If excel freezes, try opening Taxprep to make sure it didn’t show a pop-up.
  • If the pop-up warns you that your input format is invalid, make sure you set the value in the same format that’s written in the pop-up.
  • If you’re using an intermediary cell to store the value to set, make sure it’s format is set to Text.

Using the Excel Addin functions in a VBA macro

• Use the function with Application.Run(“FunctionName”, Param1, param2, etc.).
  • Here, FunctionName is the name of the Addin function.
    • Ex: “TXP.GETCELL”
• The other parameters are the parameters of the called function (Connection string, CellID, value, etc.).
  • To get the connection strings, you can use the Evaluate function.
    • Ex: Evaluate(Connection1)
• The return value of the Application.Run call will be the return value of the called function.

How to pull data from Taxprep

How to push data to CCH ProSystem fx

Version 1.0.0.157 - 4 Apr 2017 - 15:40 EST

• Fixed a bug that hid some PFX users from the Connections Manager's list
• Fixed a bug with the Sort function of the Connections Manager's list
• You can watch a previously recorded webinar we held on March 17th : http://wolterskluwer.adobeconnect.com/p1m6chrgqrp/

Version 1.0.0.150 - 24 Mar 2017 - 17:10 EST

• Various bugs have been fixed
• You can watch a previously recorded webinar we held on March 17th

Version 1.0.0.136 - 14 Mar 2017 - 16:10 EST

• Released the Version 2 of the cross-border sample called : "US Citizens Residing in Canada (No US income)
• Various bugs have been fixed
• We increased the performance of the Add-in.
• It is now possible to pull data from Pro System fx Tax using the PFX.GETCELL formula

Version 1.0.0.105 - 24 Nov 2016 - 14:27 EST

• Various small bug fixes

Version 1.0.0.104 - 17 Nov 2016 - 15:20 EST

• We fixed some problem when working with Excel 2010. We still advise you to upgrade to Excel 2013 or later versions if you can. Seems like Excel 2010 does not enjoy our add-in too much.

Version 1.0.0.103 - 16 Nov 2016 - 10:00 EST

• Fixed some bugs that would cause Taxprep to Pfx to crash under certain conditions.

Version 1.0.0.102 - 11 Nov 2016 - 10:00 EST

• Fixed a bug where Taxprep to PFX did not work for Excel 2010. It now does. However, we still do recommend that you upgrade to Office 2013 for better stability.
• Taxprep to Pfx can now push data into a Taxprep return allowing you to save that information in the Taxprep file.
  
  Previously, Taxprep to Pfx could only push data into a hidden copy of the Taxprep return you connected in the Connection Manager. Even if the actual Taxprep file was open. Taxprep could then push information into that hidden return, do calculation and you could pull the results out of it but there was no way to save that hidden copy.
  
  But now, Taxprep to Pfx will push information directly into a Taxprep return, when that return is open in Taxprep. It is very important that the return be open in Taxprep while data is being pushed to it in order for you to be able save it later. This new feature, effectively allows you to use Taxprep to Pfx to do data-entry in Taxprep instead of just performing calculations.
  
  If you have not done so yet, we invite you to click the button below to learn how to download, install and use Taxprep to Pfx.

Version 1.0.0.97 - 13 Oct 2016 - 16:00 EST

• Bug fix

Version 1.0.0.96 - 12 Oct 2016 - 15:52 EST

• Fixed bug where the Connection manager would not show PFX 2014 returns
• Change the sample files for both Taxprep and PFX to 2015 sample files
• Added a column when selecting a PFX connection in the Connection manager to show the year of PFX returns
• Added Help icon to point to Taxprep to PFX landing page

Version 1.0.0.95 - 12 Oct 2016

• Bug fix

Version 1.0.0.94 - 12 Oct 2016

• The icon of the Version now points to this Release History Page

Version 1.0.0.93 - 6 Oct 2016

• Spelling mistakes correction in formula definitions
• Bug fixes

Q. During the add-in installation, how can I fix the error about Visual Studio Tools?

A. You may see the following error during the installation:

Follow these steps to fix the error:

1. In your list of installed programs, uninstall Visual Studio Tools for Office Runtime.
2. Delete the complete VSTO folder:
   C:\Program Files (x86)\Common Files\Microsoft Shared\VSTO
3. Reinstall Visual Studio Tools for Office Runtime by downloading it from here: https://www.microsoft.com/en-US/download/details.aspx?id=48217

To correctly execute the Excel Add-in for Taxprep , your computer system must have the following:

Supported operating systems

• Windows 7 SP1 (32-bit and 64-bit) except for Starter and Home Editions* End of support: Microsoft will stop supporting Windows 7 and Windows Server 2008 R2 SP1 in January 2020. Since it is our policy to align our support of operating systems with software vendors, take note that the versions of our software applications that will be released starting in November 2019 will no longer support Windows 7 and Windows Server 2008 R2 SP1.
• Windows 8.1 (32-bit and 64-bit)

• Windows 10
• Windows Server 2008 R2 SP1
• Windows Server 2012 SP1 and R2
• Windows Server 2016

* For more information on the different Windows Editions, and their respective minimum requirements, consult the Microsoft Web site:

For Windows 7: https://support.microsoft.com/en-us/help/10737/windows-7-system-requirements

For Windows 8.1: https://windows.microsoft.com/en-CA/windows-8/system-requirements

For Windows 10: https://www.microsoft.com/en-ca/windows/windows-10-specifications#sysreqs

Important: While supported, Windows 7 SP1 and Windows Server 2008 R2 SP1 do not have SMB 3.0 encryption for data in transit and are not a recommended system requirement for this product. We strongly recommend that all customers enable the enhanced security measures available in Windows 8.1 or higher operating system, including SMB 3.0 encryption for data in transit. Since the configuration of SMB 3.0 is explicit, please consult this Microsoft article to proceed with its configuration.

Microsoft .Net Framework

Microsoft .Net Framework v.4.7.2 is required.

Mircrosoft Excel

Microsoft 2010 and later